{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 作业6\n",
    "（将给定的素材data文件夹中，excel文件中超过68度和低于32度的数据分别存入高温和低温excel中，记录日期时间和温度)\n",
    "### 通义千问：\n",
    "生成一段程序，将C:\\python作业\\data中每个工作表下温度大于68度提取出来并记录时间，日期，将文件名（不带.xlsx）作为日期，然后汇总以工作表形式保存到C:\\python作业下高温.xlsx  ，将C:\\python作业\\data中每个工作表下温度小于32度提取出来并记录时间，日期，将文件名（不带.xlsx）作为日期，然后汇总以工作表形式保存到C:\\python作业下低温.xlsx\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "\n",
    "# 定义文件夹路径\n",
    "data_folder = r'C:\\python作业\\data'\n",
    "high_temp_file = r'C:\\python作业\\高温.xlsx'\n",
    "low_temp_file = r'C:\\python作业\\低温.xlsx'\n",
    "\n",
    "# 初始化用于存储结果的字典\n",
    "high_temp_data = {}\n",
    "low_temp_data = {}\n",
    "\n",
    "# 遍历data文件夹中的所有Excel文件\n",
    "for file in os.listdir(data_folder):\n",
    "    if file.endswith('.xlsx'):\n",
    "        file_path = os.path.join(data_folder, file)\n",
    "        date = file[:-5]  # 去掉.xlsx后缀获取日期\n",
    "        \n",
    "        # 读取Excel文件中的所有工作表\n",
    "        xls = pd.ExcelFile(file_path)\n",
    "        for sheet_name in xls.sheet_names:\n",
    "            df = pd.read_excel(xls, sheet_name=sheet_name)\n",
    "            \n",
    "            # 添加日期列\n",
    "            df['日期'] = date\n",
    "            \n",
    "            # 提取温度大于68度的数据\n",
    "            high_temp_df = df[df['温度'] > 68][['时间', '日期', '温度']]\n",
    "            if not high_temp_df.empty:\n",
    "                if sheet_name in high_temp_data:\n",
    "                    high_temp_data[sheet_name] = pd.concat([high_temp_data[sheet_name], high_temp_df])\n",
    "                else:\n",
    "                    high_temp_data[sheet_name] = high_temp_df\n",
    "            \n",
    "            # 提取温度小于32度的数据\n",
    "            low_temp_df = df[df['温度'] < 32][['时间', '日期', '温度']]\n",
    "            if not low_temp_df.empty:\n",
    "                if sheet_name in low_temp_data:\n",
    "                    low_temp_data[sheet_name] = pd.concat([low_temp_data[sheet_name], low_temp_df])\n",
    "                else:\n",
    "                    low_temp_data[sheet_name] = low_temp_df\n",
    "\n",
    "# 将结果保存到新的Excel文件中\n",
    "with pd.ExcelWriter(high_temp_file) as writer_high:\n",
    "    for sheet_name, data in high_temp_data.items():\n",
    "        data.to_excel(writer_high, sheet_name=sheet_name, index=False)\n",
    "\n",
    "with pd.ExcelWriter(low_temp_file) as writer_low:\n",
    "    for sheet_name, data in low_temp_data.items():\n",
    "        data.to_excel(writer_low, sheet_name=sheet_name, index=False)\n",
    "\n",
    "\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
